<?php

include_once dirname(__FILE__) . "/error.php";
include_once dirname(__FILE__) . "/log.php";
include_once dirname(__FILE__) . "/conf.php";

class Db{

    private $conn = false;

    public function get_top_dish()
    {
        global $err_code;

        $top = (string)Conf::get("dish.list.top");
        if($top == false)
        {
            $err_code = ec_conf_error;
            Log::error("dish.list.top conf error");
            return false;
        }
        $top = (int)$top;
        $sql = "select * from dish_table order by dish_vote desc limit $top";
        $ret = $this->execute($sql);
        if($ret == false)
        {
            Log::error("get_top_dish() failed");
        }
        else
        {
            Log::debug("top dish:" . json_encode($ret));
        }
        return $ret;
    }
    
    public function get_home_dish($arg)
    {
        global $err_code;
        
        if(!is_array($arg) || !array_key_exists("province", $arg) ||
            !array_key_exists("city", $arg) )
        {
            $err_code = ec_param_error;
            Log::debug("arg:" . var_export($arg, true));
            Log::error("get_home_dish params error");
            return false;
        }

        $home_num = (string)Conf::get("dish.list.home");
        if($home_num == false)
        {
            $err_code = ec_conf_error;
            Log::error("dish.list.home conf error");
            return false;
        }
        $home_num = (int)$home_num;

        $prov = $arg["province"];
        $city = $arg["city"];
         
        $sql = "select * from dish_table where dish_region like '%$prov%' or " . 
                "dish_family like '%$city%' order by dish_vote";
        Log::debug("sql: $sql");
        $ret = $this->execute($sql);
        
        return $ret;
    }

    public function is_new_user($arg)
    {
        global $err_code;
        
        if(!is_array($arg) || !array_key_exists("open_id", $arg))
        {
            $err_code = ec_param_error;
            Log::error("is_new_user params error");
            return false;
        }
        $open_id = $arg['open_id'];

        $sql = "select * from user_table where open_id = '$open_id'";
        Log::debug("sql: $sql");
        $ret = $this->execute($sql);
        
        return $ret;
    }
    
    public function get_random_dish($arg)
    {
        global $err_code;
        
        if(!is_array($arg) )
        {
            $err_code = ec_param_error;
            Log::error("get_random_dish params error");
            return false;
        }
        
        $rand_num = (string)Conf::get("dish.list.rand");
        if($rand_num == false)
        {
            $err_code = ec_conf_error;
            Log::error("dish.list.rand conf error");
            return false;
        }
        $rand_num = (int)$rand_num;

        $total_num = (string)Conf::get("dish.list.total");
        if($total_num == false)
        {
            $err_code = ec_conf_error;
            Log::error("dish.list.total conf error");
            return false;
        }
        $total_num = (int)$total_num;
        
        $left_num = $total_num - count($arg);
        $rand_num = $rand_num > $left_num ? $rand_num : $left_num;
        
        $excl_list = "";
        foreach($arg as $dish)
        {
            $excl_list = $excl_list . $dish['dish_id'] . ",";
        }
        $excl_list = trim($excl_list, ",");

        $sql = "select * from dish_table where dish_id not in ($excl_list) order by rand() limit $rand_num";
        Log::debug("sql: $sql");
        $ret = $this->execute($sql);
        if(is_array($ret)  && count($ret) < $rand_num )
        {
            $err_code = ec_dish_not_enough_error;
            Log::error("can not get enough dish error");
            return false;
        }
        return $ret;
    }

    public function get_eaten_dish($arg)
    {
        global $err_code;
        
        if(!is_array($arg) || !array_key_exists("open_id", $arg) )
        {
            $err_code = ec_param_error;
            Log::error("get_eaten_dish params error");
            return false;
        }
        $open_id = $arg['open_id'];

        $eaten_num = (string)Conf::get("dish.list.eaten");
        if($eaten_num == false)
        {
            $err_code = ec_conf_error;
            Log::error("dish.list.eaten conf error");
            return false;
        }
        $eaten_num = (int)$eaten_num;

        $sql = "select * from dish_table where dish_id in ( select dish_id from " .
             "favor_table where open_id = '$open_id') limit $eaten_num";
        Log::debug("sql: $sql");
        $ret = $this->execute($sql);
        
        return $ret;
    }

    public function get_friend_dish($arg)
    {
        global $err_code;
        
        if(!is_array($arg) || !array_key_exists("open_id", $arg) )
        {
            $err_code = ec_param_error;
            Log::error("get_friend_dish params error");
            return false;
        }
        $open_id = $arg['open_id'];

        $friend_num = (string)Conf::get("dish.list.friend");
        if($friend_num == false)
        {
            $err_code = ec_conf_error;
            Log::error("dish.list.friend conf error");
            return false;
        }
        $friend_num = (int)$friend_num;

        $sql = "select * from dish_table where dish_id in( select dish_id from favor_table ". 
            "where open_id in (select friend from friend_table where open_id = '$open_id' ))";
        Log::debug("sql: $sql");
        $ret = $this->execute($sql);
        
        return $ret;
    }

    public function update_dish_vote($arg)
    {
        global $err_code;
        
        if(!is_array($arg) || !array_key_exists("open_id", $arg) || !array_key_exists("selected", $arg ) 
            || !is_array($arg["selected"]) )
        {
            $err_code = ec_param_error;
            Log::error("update_dish_vote params error");
            return false;
        }
        $open_id = $arg['open_id'];
        $selected = $arg['selected'];
        if(count($selected) == 0)
        {
            Log::debug("empty selected, skipped.");
            return true;
        }
        $succ = 0;
        foreach($selected as $dish_id)
        {
            //get dish family
            $sql = "select dish_family from dish_table where dish_id = $dish_id";
            $ret = $this->execute($sql);
            if($ret === false)
            {
                $err_code = ec_db_error;
                Log::error("update_dish_vote db error");
                return false;
            }  
            if(count($ret) == 0)
            {
                Log::debug("skip dish_id[$dish_id], no dish_family");
                continue;
            }
            $family = $ret[0]['dish_family'];

            // check favor existence
            $sql = "select * from favor_table where open_id = '$open_id' and dish_id = '$dish_id'";
            $ret = $this->execute($sql);
            if($ret === false)
            {
                $err_code = ec_db_error;
                Log::error("check favor existence error");
                return false;
            }  
            if(count($ret) == 0)
            {
                $sql = "replace into favor_table values('$open_id', $dish_id, '$family', 1)"; 
                Log::debug("new user favor[$open_id, $dish_id] found");
            }
            else
            {
                $count = (int)$ret[0]['count'] + 1;
                $sql = "update favor_table set count = $count where open_id = '$open_id' and " .
                    " dish_id = $dish_id";
            }
            Log::debug("sql:$sql");
            $ret = $this->execute($sql);
            if($ret == false )
            {
                $err_code = ec_db_error;
                Log::error("update favor table error");
                return false;
            }  
            $succ ++;
        }
        if($succ == 0)
        {
            $err_code = ec_update_favor_error;
            Log::error("update favor table error");
            return false;
        }
        else
        {
            return true;
        }
    }
    public function get_similar_dish_family_user($arg)
    {
        global $err_code;
        
        if(!is_array($arg) || !array_key_exists("open_id", $arg) || !array_key_exists("selected", $arg ) 
            || !is_array($arg["selected"]) )
        {
            $err_code = ec_param_error;
            Log::error("get_similar_dish_family_user params error");
            return false;
        }
        $open_id = $arg['open_id'];
        $selected = $arg['selected'];
        $succ = 0;
        $all = array();
        foreach($selected as $dish_id)
        {
            //get dish family
            $sql = "select dish_family from dish_table where dish_id = $dish_id";
            $ret = $this->execute($sql);
            if($ret === false)
            {
                $err_code = ec_db_error;
                Log::error("get dish family error");
                return false;
            }  
            if(count($ret) == 0)
            {
                Log::debug("skip dish_id[$dish_id], no dish_family");
                continue;
            }
            $family = $ret[0]['dish_family'];

            $sql = "select * from user_table where open_id in (select open_id from " . 
                "favor_table where dish_family like '%$family%')";
            Log::debug("sql:$sql");
            $ret = $this->execute($sql);
            if($ret === false)
            {
                $err_code = ec_db_error;
                Log::error("get similar user error");
                return false;
            }
            foreach($ret as $one)
            {
                $item = array(
                    $one['open_id'] => $one,
                );
                $all = array_merge($all, $item);
            }
        }
        unset($all[$open_id]);
        return $all;
    }
    
    public function get_similar_dish_user($arg)
    {
        global $err_code;
        
        if(!is_array($arg) || !array_key_exists("open_id", $arg) || !array_key_exists("selected", $arg ) 
            || !is_array($arg["selected"]) || !array_key_exists("num", $arg) )
        {
            $err_code = ec_param_error;
            Log::error("get_similar_dish_user params error");
            return false;
        }
        $open_id = $arg['open_id'];
        $selected = $arg['selected'];
        $num = (int)$arg['num'];
        $succ = 0;
        $all = array();
        foreach($selected as $dish_id)
        {
            //get dish family
            $sql = "select dish_family from dish_table where dish_id = $dish_id";
            $ret = $this->execute($sql);
            if($ret === false)
            {
                $err_code = ec_db_error;
                Log::error("get dish family error");
                return false;
            }  
            if(count($ret) == 0)
            {
                Log::debug("skip dish_id[$dish_id], no dish_family");
                continue;
            }
            $family = $ret[0]['dish_family'];

            $sql = "select * from user_table where open_id in (select open_id from " . 
                "favor_table where dish_id in (select dish_id from favor_table where " .
                "open_id = '$open_id'))";
            Log::debug("sql:$sql");
            $ret = $this->execute($sql);
            if($ret === false)
            {
                $err_code = ec_db_error;
                Log::error("get similar user error");
                return false;
            }
            foreach($ret as $one)
            {
                $item = array(
                    $one['open_id'] => $one,
                );
                $all = array_merge($all, $item);
            }
        }
        if(array_key_exists($open_id, $all))
        {
            unset($all[$open_id]);
        }
        return $all;
    }
    public function get_random_user($arg)
    {
        global $err_code;
        
        if(!is_array($arg) || !array_key_exists("open_id", $arg) ||
            !array_key_exists("num", $arg) )
        {
            $err_code = ec_param_error;
            Log::error("get_random_user params error");
            return false;
        }
        $open_id = $arg['open_id'];
        $num = (int)$arg['num'];
        $all = array();

        $sql = "select * from user_table where open_id not in ('$open_id') order by rand() limit $num";
        $ret = $this->execute($sql);
        if($ret === false)
        {
            $err_code = ec_db_error;
            Log::error("recommend rand user error");
            return false;
        }
        foreach($ret as $one)
        {
            $item = array(
                $one['open_id'] => $one,
            );
            $all = array_merge($all, $item);
        }
        return $all;
    }
    
    public function add_similar_tag($args, $list)
    {
        global $err_code;
        
        if(!is_array($args) || !array_key_exists("open_id", $args) || !is_array($list))
        {
            $err_code = ec_param_error;
            Log::error("add_similar_tag params error");
            return false;
        }
        
        $open_id = $args['open_id'];
        $sql = "select dish_family from favor_table where open_id in ('$open_id')";
        $cur = $this->execute($sql);
        if($cur === false)
        {
            $err_code = ec_db_error;
            Log::error("get dish family error");
            return false;
        }
        
        $cur_dish_family = "";
        foreach($cur as $dish)
        {
            $cur_dish_family = $cur_dish_family . $dish['dish_family'];
        }
        
        $all = array();
        foreach($list as $cur)
        {
            $user_id = $cur['open_id'];
            $sql = "select dish_family from favor_table where open_id in ('$user_id')";
            $users = $this->execute($sql);
            if($users === false)
            {
                $err_code = ec_db_error;
                Log::error("get dish family error");
                return false;
            }

            $sim = "";
            foreach($users as $user_dish_family)
            {
                $dish_family = $user_dish_family['dish_family'];
                $found = strpos($cur_dish_family, $dish_family);
                if($found >= 0)
                {
                    $sim = $sim . $dish_family;
                }
            }
            
            $one = array(
                "sim" => $sim,
            );
            $user = array_merge($cur, $one);
            Log::debug("user:" .var_export($user, true));
            array_push($all, $user);
        }

        return $all;
    }

    public function get_user_vote($args, $rest_id)
    {
        global $err_code;
        
        if(!is_array($args) || !array_key_exists("open_id", $args) || !$rest_id)
        {
            $err_code = ec_param_error;
            Log::error("get_user_vote params error");
            return false;
        }
        $open_id = $args['open_id'];
        
        $sql = "select count(*) as count from visit_rest_table where open_id = '$open_id' " . 
            "and rest_id =$rest_id";
        $ret = $this->execute($sql);
        if($ret === false)
        {
            $err_code = ec_db_error;
            Log::error("get user vote error");
            return false;
        }
        return $ret[0]['count'];
    }
    
    public function float_once_buddy($list, $arg)
    {
        global $err_code;
        
        if(!is_array($arg) || !array_key_exists("open_id", $arg) || !is_array($list) || 
            !array_key_exists("num", $arg) )
        {
            $err_code = ec_param_error;
            Log::error("float_once_buddy params error");
            return false;
        }

        $open_id = $arg['open_id'];
        $num = (int)$arg['num'];
        $sql = "select * from friend_table where open_id = '$open_id'";
        $ret = $this->execute($sql);
        if($ret === false)
        {
            $err_code = ec_db_error;
            Log::error("get friend error");
            return false;
        }  
        $all = array();
        foreach($ret as $friend)
        {
            $id = $friend['friend'];
//            $sql = "select * from user_table where open_id in ('$open_id', '$id')";
//            $ret = $this->execute($sql);
//            if($ret === false)
//            {
//                $err_code = ec_db_error;
//                Log::error("get friend error");
//                return false;
//            }
//            $gender = 0;
//            foreach($ret as $one)
//            {
//                $gender += (int)$one['gender'];
//            }
        
            if(array_key_exists($id, $list) )
            {
                $item = array(
                    $id =>  $list[$id],
                );
                $all = array_merge($all, $item);
                unset($list[$id]);
            }
        }
        $all = array_merge($all, $list);
        $ret = array_slice($all, 0, $num);
        return $ret;
    }
    public function get_rest_vote($arg)
    {
        global $err_code;
        
        if(!is_array($arg) || !array_key_exists("dish_list", $arg) || 
            !array_key_exists("num", $arg) )
        {
            $err_code = ec_param_error;
            Log::error("get_rest_vote params error");
            return false;
        }
        
        $num = (int)$arg['num'];
        $dish_list = explode(",", $arg['dish_list']);
            Log::debug("dish_list:" . var_export($dish_list, true));

        $all = array();
        foreach($dish_list as $one)
        {
            //get dish family
            $dish_id = (int)$one;
            $sql = "select dish_family from dish_table where dish_id = $dish_id";
            Log::debug("sql:$sql");
            $ret = $this->execute($sql);
            if($ret === false)
            {
                $err_code = ec_db_error;
                Log::error("get dish family error");
                return false;
            }  
            if(count($ret) == 0)
            {
                Log::debug("skip dish_id[$dish_id], no dish_family");
                continue;
            }
            $family = $ret[0]['dish_family'];

            $sql = "select * from rest_table where rest_tag like '%$family%'";
            Log::debug("sql:$sql");
            $ret = $this->execute($sql);
            if($ret === false)
            {
                $err_code = ec_db_error;
                Log::error("get friend error");
                return false;
            }
            foreach($ret as $item)
            {
                array_push($all, $item);
            }
        }
        if(count($all) < $num)
        {
            Log::debug("rest not enough, recommend some rand ones");
            $num = $num - count($all);
            $sql = "select * from rest_table order by rand() limit $num";
            $ret = $this->execute($sql);
            if($ret === false)
            {
                $err_code = ec_db_error;
                Log::error("get friend error");
                return false;
            }
            foreach($ret as $item)
            {
                array_push($all, $item);
            }
        }
       $all = array_unique($all, SORT_REGULAR);
        return $all;
    }

    public function init()
    {
        global $err_code;
       
        $host = (string)Conf::get("db.host");
        $port = (string)Conf::get("db.port");
        $user = (string)Conf::get("db.user");
        $pwd = (string)Conf::get("db.pwd");
        $db = (string)Conf::get("db.name");
        if($host == false || $port == false || $user == false || $pwd == false || $db == false)
        {
            $err_code = ec_db_conf_error;
            Log::error("db conf error");
            return false;
        }
        $url = $host. ":" . $port;
        $this->conn = mysql_connect($url, $user, $pwd);
        if(!$this->conn)
        {
            $err_code = ec_db_error;
            Log::error("db connect failed");
            $this->conn = false;
            return false;
        }
        $ret = mysql_select_db($db);
        if(!$ret)
        {
            $err_code = ec_db_error;
            Log::error("db select db failed");
            $this->conn = false;
            return false;
        }
        return true;
    } 
    
    public function execute($sql)
    {
        global $err_code;
        
        if(!$this->conn)
        {
            Log::error("db not init error");
            return false;
        }
        mysql_query("set names utf8"); //中文乱码问题

        $ret = mysql_query($sql);
        if($ret == false) // failed
        {
            $err_code = ec_db_error;
            Log::error("db query db failed");
            return false;
        }
        else if($ret === true) // update succ
        {
            $rows = mysql_affected_rows();
            Log::debug("db update ok, affected rows:$rows");
            return $rows;
        }
        else // select succ
        {
            $all = array();
            do{
                $row = mysql_fetch_array($ret, MYSQL_ASSOC);
                if($row)
                {
                    array_push($all, $row);
                }
            }while($row);
            $lines = count($all);
            Log::debug("db query ok, total rows:$lines");
            return $all;
        }
    }
    public function close()
    {
        if($this->conn)
        {
            mysql_close($this->conn);
        }
    }
};

?>
